package com.example.librarymanagementsystembackend.systemicfunctions.controller;

import com.example.librarymanagementsystembackend.bookmanagement.entity.BookInformation;
import com.example.librarymanagementsystembackend.bookmanagement.service.BookService;
import com.example.librarymanagementsystembackend.systemicfunctions.entity.SystemEntity;
import com.example.librarymanagementsystembackend.systemicfunctions.entity.SystemSettingsEntity;
import com.example.librarymanagementsystembackend.systemicfunctions.service.SystemService;
import com.example.librarymanagementsystembackend.utils.*;
//import com.mysql.cj.result.Row;
//import javafx.scene.control.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.Resource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.apache.poi.ss.usermodel.Cell;

import java.io.*;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@RestController
@RequestMapping("/system")
@CrossOrigin(origins = "http://localhost:8080")  //和Vue框架跨域 连接
public class SystemController {

    //新增接口为： userConfiguration
    @Autowired
    SystemService systemService;

    @Autowired
    FileUtils fileUtils;

    @Autowired
    private BookService bookService;


    //查询用户的配置数据库系统，并且返回页面(如:用户背景颜色，URL啥啥的)
    @GetMapping("/userConfiguration")
    public ResponseData updateUserConfiguration(){
        return ResponseDataUtils.success("操作成功");
    }

    //实现下载sql
    @GetMapping("/bookSql")
    public ResponseEntity<Resource> exportBookSqlToLocal() {
        try {
            List<BookInformation> books = systemService.bookInformation();
            if (books == null || books.isEmpty()) {
                throw new RuntimeException("没有图书信息可导出");
            }

            StringBuilder sqlBuilder = new StringBuilder();

            // 1. 生成 DROP TABLE 语句（严格匹配正则格式）
            sqlBuilder.append("DROP TABLE IF EXISTS `books`;\n\n");

            // 2. 生成 CREATE TABLE 语句（完整映射正则定义的字段）
            sqlBuilder.append("CREATE TABLE `books` (\n")
                    .append("  `id` int(11) NOT NULL AUTO_INCREMENT,\n")
                    .append("  `isbn` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,\n")
                    .append("  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,\n")
                    .append("  `author` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,\n")
                    .append("  `coverUrl` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,\n")
                    .append("  `stock` int(11) NOT NULL DEFAULT 0,\n")
                    .append("  `categoryId` int(11) DEFAULT NULL,\n")
                    .append("  `createdAt` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,\n")
                    .append("  `updatedAt` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),\n")
                    .append("  PRIMARY KEY (`id`) USING BTREE,\n")
                    .append("  UNIQUE INDEX `isbn` (`isbn`) USING BTREE,\n")
                    .append("  INDEX `idx_books_category` (`categoryId`) USING BTREE,\n")
                    .append("  CONSTRAINT `books_ibfk_1` FOREIGN KEY (`categoryId`) REFERENCES `bookcategories` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT\n")
                    .append(") ENGINE=InnoDB AUTO_INCREMENT=30 CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=Dynamic;\n\n");

            // 3. 生成数据插入语句（带字段转义）
            sqlBuilder.append("-- Records of books\n");
            for (BookInformation book : books) {
                String sql = String.format(
                        "INSERT INTO books (id, isbn, title, author, coverUrl, stock, categoryId, createdAt, updatedAt) " +
                                "VALUES (%d, '%s', '%s', '%s', '%s', %d, %d, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);\n",
                        book.getId(),
                        escapeSql(book.getIsbn()),
                        escapeSql(book.getTitle()),
                        escapeSql(book.getAuthor()),
                        escapeSql(book.getCoverUrl()),
                        book.getStock(),
                        book.getCategoryId()
                );
                sqlBuilder.append(sql);
            }

            byte[] sqlBytes = sqlBuilder.toString().getBytes(StandardCharsets.UTF_8);
            ByteArrayResource resource = new ByteArrayResource(sqlBytes);

            return ResponseEntity.ok()
                    .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=books.sql")
                    .contentType(MediaType.TEXT_PLAIN)
                    .contentLength(sqlBytes.length)
                    .body(resource);

        } catch (Exception e) {
            e.printStackTrace();
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(null);
        }
    }

    // 工具方法：转义SQL特殊字符
    private String escapeSql(String input) {
        if (input == null) return "NULL";
        return input.replace("'", "''").replace("\\", "\\\\");
    }

    // 工具方法：格式化时间戳（假设BookInformation包含java.util.Date类型字段）
    private String formatTimestamp(Date timestamp) {
        if (timestamp == null) return "CURRENT_TIMESTAMP";
        return "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(timestamp) + "'";
    }

    //接口实现：实现查询图书信息，并且导出Excel文件
    @GetMapping("/bookInformation")
    public ResponseEntity<Resource> exportBookToLocal() {
        try {
            // 获取图书数据
            List<BookInformation> books = systemService.bookInformation();
            if (books == null || books.isEmpty()) {
                throw new RuntimeException("没有图书信息可导出");
            }

            // 创建 Excel 工作簿
            try (Workbook workbook = new XSSFWorkbook()) {
                Sheet sheet = workbook.createSheet("图书信息");

                // 表头
                Row headerRow = sheet.createRow(0);
                String[] headers = {"ID", "书名", "作者", "ISBN", "库存数量","库存数量(状态)","所属分类ID"};
                for (int i = 0; i < headers.length; i++) {
                    Cell cell = headerRow.createCell(i);
                    cell.setCellValue(headers[i]);
                }

                // 数据行
                int rowNum = 1;
                for (BookInformation book : books) {
                    Row row = sheet.createRow(rowNum++);
                    row.createCell(0).setCellValue(book.getId());
                    row.createCell(1).setCellValue(book.getTitle());
                    row.createCell(2).setCellValue(book.getAuthor());
                    row.createCell(3).setCellValue(book.getIsbn());
                    row.createCell(4).setCellValue(book.getStock() > 0 ? "在库" : "借出");
                    row.createCell(5).setCellValue(book.getCategoryId());
                }

                // 将 Excel 写入字节数组
                ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
                workbook.write(byteArrayOutputStream);
                byte[] excelBytes = byteArrayOutputStream.toByteArray();

                // 构建 Resource 对象
                ByteArrayResource resource = new ByteArrayResource(excelBytes);

                // 返回 ResponseEntity
                return ResponseEntity.ok()
                        .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=books.xlsx")
                        .contentType(MediaType.APPLICATION_OCTET_STREAM)
                        .contentLength(excelBytes.length)
                        .body(resource);

            }
        } catch (IOException e) {
            return ResponseEntity.status(500).body(null);
        }
    }






    //接口实现，先执行查询数据库里的借书什么的是否有值，没有就给默认。
    @GetMapping("/parameters")
    public SystemSettingsEntity getParameters() {
        SystemSettingsEntity settings = systemService.getSystemSettings();

        try {
            if (settings == null) {
                settings = new SystemSettingsEntity();
                settings.setBorrowLimit(5);
                settings.setBorrowPeriod(30);
                settings.setDailyOverdueFine(new BigDecimal("0.5"));
            } else {
                System.out.println("从数据库获取到配置：" + settings);
            }
        }catch (InvalidRequestException e){
            throw new InvalidRequestException("信息查询失败（" + e.getMessage() + "）");
        }


        return settings;
    }

    //接口实现：实现管理员提交新的借书限额、借书期限、每日逾期后的具体的罚金;并且更新数据库，默认参数为 5 、30 、 0.5
    @PutMapping("/parameters")
    public ResponseData updateParameters(@RequestBody SystemSettingsEntity systemSettingsEntity) {
        int row = systemService.updateParameters(systemSettingsEntity.getBorrowLimit(), systemSettingsEntity.getBorrowPeriod(), systemSettingsEntity.getDailyOverdueFine());
        if (row>0){
            return ResponseDataUtils.success("更新成功");
        }else{
            return ResponseDataUtils.success(   "更新失败");
        }
    }

    //接口实现，当点击重置默认后，借阅管理会自动的填充默认值 5 30 0.5
    @PutMapping("/parameters2")
    public ResponseData resetDefaultParameters(@RequestBody SystemSettingsEntity systemSettingsEntity) {
        // 直接写入默认值
        systemSettingsEntity.setBorrowLimit(5);
        systemSettingsEntity.setBorrowPeriod(30);
        systemSettingsEntity.setDailyOverdueFine(new BigDecimal("0.5"));

        // 调用 service 更新数据库
        systemService.updateParameters(systemSettingsEntity.getBorrowLimit(), systemSettingsEntity.getBorrowPeriod(), systemSettingsEntity.getDailyOverdueFine());

        return ResponseDataUtils.success("已成功重置为默认值：借书限额=5，借书期限=30，罚金=0.5");
    }


//    //接口实线：当管理员点击重置默认后，更改系统设置的借阅设置的默认参数：为 5 、 30 、 0.5
//    @PutMapping("/resetTheDefault")
//    public String resetTheDefault() {
//        return "当管理员点击重置默认后，更改系统设置的借阅设置的默认参数：为 5 、 30 、 0.5";
//    }   <！弃用！>


    //接口实现：实现普通用户更改用户名和密码;
    @PutMapping("/{id}/userBasicInformation")
    public ResponseData userBasicInformation(@RequestBody SystemEntity systemEntity) {
        int row = systemService.userBasicInformation(systemEntity.getId(), systemEntity.getUsername(), systemEntity.getPassword());

        try {
            if (row > 0) {
                return ResponseDataUtils.success("更新成功");
            } else {
                return ResponseDataUtils.success("更新失败");
            }
        } catch (InvalidRequestException e) {
            throw new InvalidRequestException("未有更新等（" + e.getMessage() + "）");
        }

    }

    //接口实现：实现普通用户更改头像和修改背景颜色;
    @PutMapping(value = "/{id}/userExpansionInformation", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
    public ResponseData userExpansionInformation(
            @RequestPart("systemEntity") SystemEntity systemEntity,
            @RequestPart(name = "avatar", required = false) MultipartFile avatarFile) {

        String avatarUrl = systemEntity.getAvatarUrl();

        if (avatarFile != null && !avatarFile.isEmpty()) {
            String relativePath = fileUtils.addFile("",avatarFile);
            if (relativePath == null) {
                return ResponseDataUtils.success("头像上传失败");
            }
            avatarUrl =  relativePath.replace("\\", "/");
        }

        int row = systemService.updateUserExpansionInformation(systemEntity.getId(), avatarUrl, systemEntity.getBackgroundColor());
        if (row > 0) {
            return ResponseDataUtils.success(avatarUrl);
        } else {
            return ResponseDataUtils.success("更新失败");
        }
    }

    //接口实现：实现用户提交sql文件后，分析里面的字段内容，如果符合本数据库的内容格式就进行更新。
    @PutMapping(path = "/booksupdate", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
    public ResponseData updateBooks(@RequestPart("file") MultipartFile file) {
        if (file.isEmpty()) {
            return ResponseDataUtils.success("未选择任何文件");
        }
        System.out.println("无关紧要");
        try {
            String dbUrl = "jdbc:mysql://localhost:60521/librarymanagementsystem?useSSL=false&serverTimezone=UTC";
            String dbUser = "root";
            String dbPassword = "250521";

            SQLFileExecutor executor = new SQLFileExecutor(dbUrl, dbUser, dbPassword);

            // 只使用一次原始 InputStream，并将其内容缓存到内存中
            executor.validateFieldsAndExecute(file.getInputStream());

            return ResponseDataUtils.success("SQL 文件字段一致且已成功执行");

        } catch (IOException e) {
            e.printStackTrace();
            return ResponseDataUtils.success("文件读取失败：" + e.getMessage());
        } catch (IllegalArgumentException e) {
            return ResponseDataUtils.success("字段校验失败：" + e.getMessage());
        } catch (SQLException e) {
            e.printStackTrace();
            return ResponseDataUtils.success("SQL 执行失败：" + e.getMessage());
        }
    }







    /*
    可配合Result封装类来使用的具体实现。
    * // 接口定义：实现管理员提交新的借书限额、借书期限、每日逾期后的具体的罚金;并且更新数据库，默认参数为 5 、30 、 0.5
    @PutMapping("/parameters")
    public Result updateParameters() {
    int rows = 0; // 模拟数据库更新行数
    return rows > 0 ? Result.success() : Result.failure();
    }

    // 接口定义：实现普通用户更改用户名和密码
    @PutMapping("/userBasicInformation")
    public Result userBasicInformation() {
    int rows = 0; // 模拟数据库更新行数
    return rows > 0 ? Result.success() : Result.failure();
    }

    // 接口定义：实现普通用户更改头像和修改背景颜色
    @PutMapping("/userExpansionInformation")
    public Result userExpansionInformation() {
    int rows = 0; // 模拟数据库更新行数
    return rows > 0 ? Result.success() : Result.failure();
   }
    * */
}

